import { query } from '@/lib/database';
import { RowDataPacket } from 'mysql2';

export type WishType = 'achieved' | 'desired';

export interface WishRecord {
  id: number;
  user_id: number;
  content: string;
  type: WishType;
  created_at: string; // ISO string
  updated_at: string; // ISO string
  achieved_at: string | null; // ISO string or null
}

// Create wishes table if not exists. Kept idempotent and safe to call often.
export async function ensureWishesTable(): Promise<void> {
  await query(
    `CREATE TABLE IF NOT EXISTS wishes (
      id INT AUTO_INCREMENT PRIMARY KEY,
      user_id INT NOT NULL,
      content TEXT NOT NULL,
      type ENUM('achieved','desired') NOT NULL,
      created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      achieved_at TIMESTAMP NULL DEFAULT NULL,
      INDEX idx_user_type (user_id, type),
      INDEX idx_type (type)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;`
  );

  // 尝试创建全文索引（用于相似度检索）。MySQL 8 对中文可使用 ngram 解析器。
  try {
    const exists = (await query(
      "SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'wishes' AND INDEX_NAME = 'idx_wishes_content' LIMIT 1"
    )) as RowDataPacket[];
    if (exists.length === 0) {
      try {
        // 优先尝试 ngram（中文更友好），不可用则回退到默认解析器
        await query("CREATE FULLTEXT INDEX idx_wishes_content ON wishes (content) WITH PARSER ngram");
      } catch {
        await query("CREATE FULLTEXT INDEX idx_wishes_content ON wishes (content)");
      }
    }
  } catch (e) {
    // 非关键错误：索引检查或创建失败则忽略，稍后查询时回退到 LIKE。
    console.warn('FULLTEXT index ensure failed:', e);
  }
}

function toWishRecord(row: any): WishRecord {
  const toISO = (v: any) => (v ? new Date(v).toISOString() : null);
  return {
    id: row.id,
    user_id: row.user_id,
    content: row.content,
    type: row.type,
    created_at: toISO(row.created_at) as string,
    updated_at: toISO(row.updated_at) as string,
    achieved_at: toISO(row.achieved_at),
  };
}

export async function getUserWishes(userId: number, opts?: { type?: WishType; q?: string }): Promise<WishRecord[]> {
  await ensureWishesTable();
  const conditions: string[] = ['user_id = ?'];
  const params: any[] = [userId];
  if (opts?.type) {
    conditions.push('type = ?');
    params.push(opts.type);
  }
  if (opts?.q) {
    conditions.push('content LIKE ?');
    params.push(`%${opts.q}%`);
  }
  const sql = `SELECT * FROM wishes WHERE ${conditions.join(' AND ')} ORDER BY created_at DESC`;
  const rows = (await query(sql, params)) as RowDataPacket[];
  return rows.map(toWishRecord);
}

export async function createWish(userId: number, content: string, type: WishType): Promise<WishRecord> {
  await ensureWishesTable();
  const insert = (await query(
    'INSERT INTO wishes (user_id, content, type) VALUES (?, ?, ?)',
    [userId, content, type]
  )) as any;
  const rows = (await query('SELECT * FROM wishes WHERE id = ?', [insert.insertId])) as RowDataPacket[];
  return toWishRecord(rows[0]);
}

export async function updateWish(
  userId: number,
  id: number,
  patch: { content?: string; type?: WishType; convertToAchieved?: boolean }
): Promise<WishRecord | null> {
  await ensureWishesTable();

  // ensure ownership
  const own = (await query('SELECT * FROM wishes WHERE id = ? AND user_id = ?', [id, userId])) as RowDataPacket[];
  if (own.length === 0) return null;

  const fields: string[] = [];
  const params: any[] = [];

  if (patch.content !== undefined) {
    fields.push('content = ?');
    params.push(patch.content);
  }

  if (patch.convertToAchieved || patch.type === 'achieved') {
    fields.push('type = ?');
    params.push('achieved');
    fields.push('achieved_at = CURRENT_TIMESTAMP');
  } else if (patch.type === 'desired') {
    fields.push('type = ?');
    params.push('desired');
    fields.push('achieved_at = NULL');
  }

  if (fields.length > 0) {
    const sql = `UPDATE wishes SET ${fields.join(', ')} WHERE id = ? AND user_id = ?`;
    params.push(id, userId);
    await query(sql, params);
  }

  const rows = (await query('SELECT * FROM wishes WHERE id = ?', [id])) as RowDataPacket[];
  return rows.length ? toWishRecord(rows[0]) : null;
}

export async function deleteWish(userId: number, id: number): Promise<boolean> {
  await ensureWishesTable();
  const res = (await query('DELETE FROM wishes WHERE id = ? AND user_id = ?', [id, userId])) as any;
  return res.affectedRows > 0;
}

export interface MatchResult {
  matchedUserId: number;
  username: string;
  email: string;
  desiredWishes: WishRecord[];
  score?: number; // 可选：相似度分数（全文检索返回）
}

export async function matchWish(userId: number, wishId: number): Promise<MatchResult[]> {
  await ensureWishesTable();
  const rowsWish = (await query('SELECT content FROM wishes WHERE id = ? AND user_id = ?', [wishId, userId])) as RowDataPacket[];
  if (rowsWish.length === 0) return [];
  const { content } = rowsWish[0] as any;
  // 首选：全文检索按相关度排序；失败时回退到 LIKE
  let candidates: RowDataPacket[] = [];
  try {
    candidates = (await query(
      `SELECT u.id AS matchedUserId, u.username, u.email,
              MAX(MATCH(aw.content) AGAINST (? IN NATURAL LANGUAGE MODE)) AS score
       FROM wishes aw
       JOIN users u ON u.id = aw.user_id
       WHERE aw.type = 'achieved' AND aw.user_id <> ?
         AND MATCH(aw.content) AGAINST (? IN NATURAL LANGUAGE MODE)
       GROUP BY u.id, u.username, u.email
       ORDER BY score DESC
       LIMIT 20`,
      [content, userId, content]
    )) as RowDataPacket[];
  } catch (e) {
    // 没有 FULLTEXT 索引或解析器不可用时，使用 LIKE 简单回退
    candidates = (await query(
      `SELECT DISTINCT u.id AS matchedUserId, u.username, u.email, 0 AS score
       FROM wishes aw
       JOIN users u ON u.id = aw.user_id
       WHERE aw.type = 'achieved' AND aw.user_id <> ? AND aw.content LIKE ?
       LIMIT 20`,
      [userId, `%${content}%`]
    )) as RowDataPacket[];
  }

  const userIds = candidates.map((c) => c.matchedUserId as number);
  if (userIds.length === 0) return [];

  // 拉取这些用户的“想实现”的愿望
  const inPlaceholders = userIds.map(() => '?').join(',');
  const desiredRows = (await query(
    `SELECT * FROM wishes WHERE type = 'desired' AND user_id IN (${inPlaceholders}) ORDER BY created_at DESC`,
    userIds
  )) as RowDataPacket[];

  // 组装结果
  const grouped = new Map<number, MatchResult>();
  for (const c of candidates) {
    grouped.set(c.matchedUserId as number, {
      matchedUserId: c.matchedUserId as number,
      username: c.username as string,
      email: c.email as string,
      desiredWishes: [],
      score: c.score ? Number(c.score) : undefined,
    });
  }
  for (const r of desiredRows) {
    const uid = r.user_id as number;
    if (grouped.has(uid)) {
      grouped.get(uid)!.desiredWishes.push(toWishRecord(r));
    }
  }
  return Array.from(grouped.values());
}